<?php
/**
 * 學期成績檔升級
 * @author cik
 *
 */
class Default_Model_Upgrade_SemeScore extends Default_Model_Upgrade_Core
{
    protected $_table="stud_seme_score";
    protected $_targetTable = array('student_score_semester');

    public function upgrade()
    {
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
            // 取出升級進程
            $cacheState = $this->_cacheFile."-state";
            $start = 0;
            if (is_file($cacheState)) {
                $state = unserialize(file_get_contents($cacheState));
                $start = $state['start'];
            }
            $query = "SELECT seme_year_seme FROM stud_seme_score GROUP BY seme_year_seme  ORDER BY seme_year_seme LIMIT $start,1";
            file_put_contents($cacheState."-$start",$query);
            $res = $this->_sfs3Pdo->query($query)->fetch(PDO::FETCH_ASSOC);
            $seme_year_seme  = $res['seme_year_seme'];

            if (!$res) {
                // 刪除 不正確資料
                $query = "DELETE FROM student_score_semester WHERE student_sn NOT IN (SELECT sn FROM student)";
                $this->_newdbPdo->exec($query);

                $query = "DELETE FROM student_score_semester WHERE subject_sn NOT IN (SELECT sn FROM semester_course)";
                $this->_newdbPdo->exec($query);

                // 更改回 INNOD
                $query = "ALTER TABLE `student_score_semester`  ENGINE = INNODB";
                $this->_newdbPdo->exec($query);
                $query = "ALTER TABLE `student_score_semester`
                ADD CONSTRAINT `student_score_semester_ibfk_1`
                FOREIGN KEY ( `student_sn` ) REFERENCES `student` (
`sn`) ON DELETE CASCADE ON UPDATE CASCADE ;";
                $this->_newdbPdo->exec($query);
                $query = "ALTER TABLE `student_score_semester`
                ADD CONSTRAINT `student_score_semester_ibfk_2`
                FOREIGN KEY ( `subject_sn` ) REFERENCES  `semester_course` (
`sn`) ON DELETE CASCADE ON UPDATE CASCADE ;";
                $this->_newdbPdo->exec($query);

                $log['upgrade_time']    = date("Y-m-d h:i:s");
                $this->finishUpgrade($log);
                 return;
            }
            else {
                if ($start == 0) {
                    // 更改 資料表為 MYISAM
                    $query = "ALTER TABLE `student_score_semester` DROP FOREIGN KEY `student_score_semester_ibfk_1`";
                    $this->_newdbPdo->exec($query) ;
                    $query = "ALTER TABLE `student_score_semester` DROP FOREIGN KEY `student_score_semester_ibfk_2` ";
                    $this->_newdbPdo->exec($query);
                    $query = "ALTER TABLE `student_score_semester`  ENGINE = MYISAM";
                    $this->_newdbPdo->exec($query);
                }

                $query = "SELECT * FROM stud_seme_score WHERE seme_year_seme='$seme_year_seme' ORDER BY  ss_update_time";
                $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
                $arr = array();
                $effortsArr = array('表現優異'=>5,'表現良好'=>4, '表現尚可'=>3,'需再加油'=>2,'有待改進'=>1);
                foreach($res as $row) {
                    unset($arr);
                    $arr['student_sn'] = $row['student_sn'];
                    $arr['subject_sn'] = $row['ss_id'];
                    $arr['score'] = $row['ss_score'];
                    $arr['description'] = $this->iconv($row['ss_score_memo']);
                    $arr['teacher_sn'] = $row['teacher_sn'];
                    $arr['update_time'] = $row['ss_update_time'];

                    // 努力程度
                    $query = "SELECT a.ss_val, b.student_sn FROM stud_seme_score_oth a, stud_base b
                 WHERE a.seme_year_seme='$seme_year_seme' AND a.stud_id=b.stud_id AND a.ss_id={$row['ss_id']} AND b.student_sn = '{$row['student_sn']}' ";
                    $effRes = $this->_sfs3Pdo->query($query)->fetch(PDO::FETCH_ASSOC);

                    $arr['efforts'] = $effortsArr[$this->iconv($effRes['ss_val'])];
                    $this->fromArray('student_score_semester',$arr);
                }

                 $state['start'] = $state['start'] +1;
                file_put_contents($cacheState,serialize($state));
                return $seme_year_seme." upgraded";
            }
        }
    }

}